﻿using Microsoft.AspNetCore.SignalR;
using Microsoft.Extensions.Configuration;
using signalr.service.broker.Hubs;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace signalr.service.broker.ConnectionSql
{
    public class SqlDependencyContext
    {
        private readonly IConfiguration configuration;
        private readonly IHubContext<ChatHub> hubContext; 
        private string conn;
        public SqlDependencyContext(IConfiguration _configuration, IHubContext<ChatHub> _hubContext)
        {
            configuration = _configuration;
            hubContext = _hubContext;
            conn = configuration.GetSection("ConnectionStrings")["SQLServerConnString"].ToString();
        }
        public void init()
        {
            SqlDependency.Start(conn);
        }
        public List<Tuple<string,string>> Update()
        {
            using (var connection=new SqlConnection(conn))
            {
                connection.Open();
                var sql = "select name,vl from  dbo.test";
                List<Tuple<string, string>> tupList = new List<Tuple<string, string>>();
                using (var command = new SqlCommand(sql, connection))
                {
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
                    using (var reader = command.ExecuteReader())
                    {
                       
                        while (reader.Read())
                        {
                            if(string.IsNullOrEmpty(reader["name"].ToString())||string.IsNullOrEmpty(reader["vl"].ToString()))
                            {
                                Console.WriteLine(reader["name"].ToString(), reader["vl"].ToString());
                                Tuple<string, string> tuple = new Tuple<string, string>(reader["name"].ToString(), reader["vl"].ToString());
                                tupList.Add(tuple);
                            }
                            
                        }
                        reader.Close();
                    }
                }
                return tupList;
            }
                
        }
        public void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            Update();
            hubContext.Clients.All.SendAsync("ReceiveMessage", "23434fsfsfh", "343434");
        }
        public void Termination()
        {
            SqlDependency.Stop(conn);
        }
    }
}
